﻿USE [Master]
IF EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name = N'ReagentStore')
	DROP DATABASE [ReagentStore];

CREATE DATABASE [ReagentStore];

SET QUOTED_IDENTIFIER OFF;
USE [ReagentStore];
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');


-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CatalogReagent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reagents] DROP CONSTRAINT [FK_CatalogReagent];

IF OBJECT_ID(N'[dbo].[FK_ReagentProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_ReagentProduct];

IF OBJECT_ID(N'[dbo].[FK_ProductRecord]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Records] DROP CONSTRAINT [FK_ProductRecord];


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Catalogs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Catalogs];

IF OBJECT_ID(N'[dbo].[Reagents]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Reagents];

IF OBJECT_ID(N'[dbo].[Products]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products];

IF OBJECT_ID(N'[dbo].[Records]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Records];


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Catalogs'
CREATE TABLE [dbo].[Catalogs] (
    [CatalogID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);


-- Creating table 'Reagents'
CREATE TABLE [dbo].[Reagents] (
    [ReagentID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Unit] nvarchar(max)  NOT NULL,
    [StandingCount] int  NOT NULL,
    [AlertCount] int  NOT NULL,
    [AlertPeriod] int  NOT NULL,
    [CatalogID] int  NOT NULL
);


-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [ProductID] int IDENTITY(1,1) NOT NULL,
    [Manufacturer] nvarchar(max)  NOT NULL,
    [Specification] nvarchar(max)  NOT NULL,
    [Batch] nvarchar(max)  NOT NULL,
    [ExpirationDate] datetime  NOT NULL,
    [ReagentID] int  NOT NULL
);


-- Creating table 'Records'
CREATE TABLE [dbo].[Records] (
    [RecordID] int IDENTITY(1,1) NOT NULL,
    [Date] datetime  NOT NULL,
    [Count] int  NOT NULL,
    [ProductID] int  NOT NULL,
    [Bill] nvarchar(max),
    [Recipient] nvarchar(max),
    [Reason] nvarchar(max)
);


-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CatalogID] in table 'Catalogs'
ALTER TABLE [dbo].[Catalogs]
ADD CONSTRAINT [PK_Catalogs]
    PRIMARY KEY CLUSTERED ([CatalogID] ASC);


-- Creating primary key on [ReagentID] in table 'Reagents'
ALTER TABLE [dbo].[Reagents]
ADD CONSTRAINT [PK_Reagents]
    PRIMARY KEY CLUSTERED ([ReagentID] ASC);


-- Creating primary key on [ProductID] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([ProductID] ASC);


-- Creating primary key on [RecordID] in table 'Records'
ALTER TABLE [dbo].[Records]
ADD CONSTRAINT [PK_Records]
    PRIMARY KEY CLUSTERED ([RecordID] ASC);


-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CatalogID] in table 'Reagents'
ALTER TABLE [dbo].[Reagents]
ADD CONSTRAINT [FK_CatalogReagent]
    FOREIGN KEY ([CatalogID])
    REFERENCES [dbo].[Catalogs]
        ([CatalogID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CatalogReagent'
CREATE INDEX [IX_FK_CatalogReagent]
ON [dbo].[Reagents]
    ([CatalogID]);


-- Creating foreign key on [ReagentID] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_ReagentProduct]
    FOREIGN KEY ([ReagentID])
    REFERENCES [dbo].[Reagents]
        ([ReagentID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ReagentProduct'
CREATE INDEX [IX_FK_ReagentProduct]
ON [dbo].[Products]
    ([ReagentID]);


-- Creating foreign key on [ProductID] in table 'Records'
ALTER TABLE [dbo].[Records]
ADD CONSTRAINT [FK_ProductRecord]
    FOREIGN KEY ([ProductID])
    REFERENCES [dbo].[Products]
        ([ProductID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductRecord'
CREATE INDEX [IX_FK_ProductRecord]
ON [dbo].[Records]
    ([ProductID]);


-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
INSERT INTO [dbo].[Catalogs] VALUES ("默认分类")